/*
 Navicat Premium Data Transfer

 Source Server         : my_database
 Source Server Type    : MySQL
 Source Server Version : 50730
 Source Host           : 127.0.0.1:3306
 Source Schema         : my_database

 Target Server Type    : MySQL
 Target Server Version : 50730
 File Encoding         : 65001

 Date: 04/06/2020 22:44:05
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for auth_group
-- ----------------------------
DROP TABLE IF EXISTS `auth_group`;
CREATE TABLE `auth_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of auth_group
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for auth_group_permissions
-- ----------------------------
DROP TABLE IF EXISTS `auth_group_permissions`;
CREATE TABLE `auth_group_permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL,
  `permission_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `auth_group_permissions_group_id_permission_id_0cd325b0_uniq` (`group_id`,`permission_id`),
  KEY `auth_group_permissio_permission_id_84c5c92e_fk_auth_perm` (`permission_id`),
  CONSTRAINT `auth_group_permissio_permission_id_84c5c92e_fk_auth_perm` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`),
  CONSTRAINT `auth_group_permissions_group_id_b120cbf9_fk_auth_group_id` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of auth_group_permissions
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for auth_permission
-- ----------------------------
DROP TABLE IF EXISTS `auth_permission`;
CREATE TABLE `auth_permission` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `content_type_id` int(11) NOT NULL,
  `codename` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `auth_permission_content_type_id_codename_01ab375a_uniq` (`content_type_id`,`codename`),
  CONSTRAINT `auth_permission_content_type_id_2f476e4b_fk_django_co` FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of auth_permission
-- ----------------------------
BEGIN;
INSERT INTO `auth_permission` VALUES (1, 'Can add log entry', 1, 'add_logentry');
INSERT INTO `auth_permission` VALUES (2, 'Can change log entry', 1, 'change_logentry');
INSERT INTO `auth_permission` VALUES (3, 'Can delete log entry', 1, 'delete_logentry');
INSERT INTO `auth_permission` VALUES (4, 'Can view log entry', 1, 'view_logentry');
INSERT INTO `auth_permission` VALUES (5, 'Can add permission', 2, 'add_permission');
INSERT INTO `auth_permission` VALUES (6, 'Can change permission', 2, 'change_permission');
INSERT INTO `auth_permission` VALUES (7, 'Can delete permission', 2, 'delete_permission');
INSERT INTO `auth_permission` VALUES (8, 'Can view permission', 2, 'view_permission');
INSERT INTO `auth_permission` VALUES (9, 'Can add user', 3, 'add_user');
INSERT INTO `auth_permission` VALUES (10, 'Can change user', 3, 'change_user');
INSERT INTO `auth_permission` VALUES (11, 'Can delete user', 3, 'delete_user');
INSERT INTO `auth_permission` VALUES (12, 'Can view user', 3, 'view_user');
INSERT INTO `auth_permission` VALUES (13, 'Can add group', 4, 'add_group');
INSERT INTO `auth_permission` VALUES (14, 'Can change group', 4, 'change_group');
INSERT INTO `auth_permission` VALUES (15, 'Can delete group', 4, 'delete_group');
INSERT INTO `auth_permission` VALUES (16, 'Can view group', 4, 'view_group');
INSERT INTO `auth_permission` VALUES (17, 'Can add content type', 5, 'add_contenttype');
INSERT INTO `auth_permission` VALUES (18, 'Can change content type', 5, 'change_contenttype');
INSERT INTO `auth_permission` VALUES (19, 'Can delete content type', 5, 'delete_contenttype');
INSERT INTO `auth_permission` VALUES (20, 'Can view content type', 5, 'view_contenttype');
INSERT INTO `auth_permission` VALUES (21, 'Can add session', 6, 'add_session');
INSERT INTO `auth_permission` VALUES (22, 'Can change session', 6, 'change_session');
INSERT INTO `auth_permission` VALUES (23, 'Can delete session', 6, 'delete_session');
INSERT INTO `auth_permission` VALUES (24, 'Can view session', 6, 'view_session');
INSERT INTO `auth_permission` VALUES (25, 'Can add news', 7, 'add_news');
INSERT INTO `auth_permission` VALUES (26, 'Can change news', 7, 'change_news');
INSERT INTO `auth_permission` VALUES (27, 'Can delete news', 7, 'delete_news');
INSERT INTO `auth_permission` VALUES (28, 'Can view news', 7, 'view_news');
INSERT INTO `auth_permission` VALUES (29, 'Can add news', 8, 'add_news');
INSERT INTO `auth_permission` VALUES (30, 'Can change news', 8, 'change_news');
INSERT INTO `auth_permission` VALUES (31, 'Can delete news', 8, 'delete_news');
INSERT INTO `auth_permission` VALUES (32, 'Can view news', 8, 'view_news');
COMMIT;

-- ----------------------------
-- Table structure for auth_user
-- ----------------------------
DROP TABLE IF EXISTS `auth_user`;
CREATE TABLE `auth_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `password` varchar(128) NOT NULL,
  `last_login` datetime(6) DEFAULT NULL,
  `is_superuser` tinyint(1) NOT NULL,
  `username` varchar(150) NOT NULL,
  `first_name` varchar(30) NOT NULL,
  `last_name` varchar(150) NOT NULL,
  `email` varchar(254) NOT NULL,
  `is_staff` tinyint(1) NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  `date_joined` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of auth_user
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for auth_user_groups
-- ----------------------------
DROP TABLE IF EXISTS `auth_user_groups`;
CREATE TABLE `auth_user_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `auth_user_groups_user_id_group_id_94350c0c_uniq` (`user_id`,`group_id`),
  KEY `auth_user_groups_group_id_97559544_fk_auth_group_id` (`group_id`),
  CONSTRAINT `auth_user_groups_group_id_97559544_fk_auth_group_id` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`),
  CONSTRAINT `auth_user_groups_user_id_6a12ed8b_fk_auth_user_id` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of auth_user_groups
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for auth_user_user_permissions
-- ----------------------------
DROP TABLE IF EXISTS `auth_user_user_permissions`;
CREATE TABLE `auth_user_user_permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `permission_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `auth_user_user_permissions_user_id_permission_id_14a6b632_uniq` (`user_id`,`permission_id`),
  KEY `auth_user_user_permi_permission_id_1fbb5f2c_fk_auth_perm` (`permission_id`),
  CONSTRAINT `auth_user_user_permi_permission_id_1fbb5f2c_fk_auth_perm` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`),
  CONSTRAINT `auth_user_user_permissions_user_id_a95ead1b_fk_auth_user_id` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of auth_user_user_permissions
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for customer
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
  `ID` char(10) NOT NULL,
  `customer_name` varchar(30) DEFAULT 'retail customer',
  `tel` char(11) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of customer
-- ----------------------------
BEGIN;
INSERT INTO `customer` VALUES ('8880517210', '朱郡三', '15556217545', '广东省江南市建党路69号');
INSERT INTO `customer` VALUES ('8880806559', '张忠洪', '16240707664', '河北省山北市解放大道18号');
INSERT INTO `customer` VALUES ('8880916120', '韩星府', '13109333222', '河南省辛集市兴国路68号');
INSERT INTO `customer` VALUES ('8881577453', '金轸府', '17677226474', '湖南省辛集市民族街56号');
INSERT INTO `customer` VALUES ('8882385442', '陶业国', '17557951068', '河北省山北市建设街21号');
INSERT INTO `customer` VALUES ('8882744886', '钱三忠', '17588257651', '湖南省高邑县建国路13号');
INSERT INTO `customer` VALUES ('8883341047', '何忠五', '11494915750', '湖北省山南市建党路65号');
INSERT INTO `customer` VALUES ('8883949946', '钱湖洪', '11097610687', '河南省高邑县建党路53号');
INSERT INTO `customer` VALUES ('8885107482', '金国带', '10201149265', '湖北省山北市兴国路3号');
INSERT INTO `customer` VALUES ('8885338876', '梁洪襟', '10149843231', '湖北省高邑县兴国路10号');
INSERT INTO `customer` VALUES ('8885882751', '冯义都', '15097927269', '广东省高邑县民族街74号');
INSERT INTO `customer` VALUES ('8886065749', '吕湖洪', '11794139473', '湖北省山南市中山路3号');
INSERT INTO `customer` VALUES ('8886319155', '严章豫', '12239430501', '河南省山南市建设街27号');
INSERT INTO `customer` VALUES ('8887511216', '卫翼故', '19903755149', '河北省江南市中山路22号');
INSERT INTO `customer` VALUES ('8887542604', '曹业分', '14917308298', '广西省江南市兴国路37号');
INSERT INTO `customer` VALUES ('8888936870', '冯三洪', '11901808181', '广西省江北市建国路65号');
INSERT INTO `customer` VALUES ('8889713233', '李国立', '15321450489', '广西省高邑县建国路31号');
INSERT INTO `customer` VALUES ('8889790834', '杨接庐', '17545762722', '河北省藁城市建党路75号');
INSERT INTO `customer` VALUES ('8889909378', '赵建地', '17926688421', '广东省藁城市建国路29号');
COMMIT;

-- ----------------------------
-- Table structure for django_admin_log
-- ----------------------------
DROP TABLE IF EXISTS `django_admin_log`;
CREATE TABLE `django_admin_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `action_time` datetime(6) NOT NULL,
  `object_id` longtext,
  `object_repr` varchar(200) NOT NULL,
  `action_flag` smallint(5) unsigned NOT NULL,
  `change_message` longtext NOT NULL,
  `content_type_id` int(11) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `django_admin_log_content_type_id_c4bce8eb_fk_django_co` (`content_type_id`),
  KEY `django_admin_log_user_id_c564eba6_fk_auth_user_id` (`user_id`),
  CONSTRAINT `django_admin_log_content_type_id_c4bce8eb_fk_django_co` FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`),
  CONSTRAINT `django_admin_log_user_id_c564eba6_fk_auth_user_id` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of django_admin_log
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for django_content_type
-- ----------------------------
DROP TABLE IF EXISTS `django_content_type`;
CREATE TABLE `django_content_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `app_label` varchar(100) NOT NULL,
  `model` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `django_content_type_app_label_model_76bd3d3b_uniq` (`app_label`,`model`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of django_content_type
-- ----------------------------
BEGIN;
INSERT INTO `django_content_type` VALUES (1, 'admin', 'logentry');
INSERT INTO `django_content_type` VALUES (4, 'auth', 'group');
INSERT INTO `django_content_type` VALUES (2, 'auth', 'permission');
INSERT INTO `django_content_type` VALUES (3, 'auth', 'user');
INSERT INTO `django_content_type` VALUES (5, 'contenttypes', 'contenttype');
INSERT INTO `django_content_type` VALUES (7, 'Django_MySQL_Table', 'news');
INSERT INTO `django_content_type` VALUES (6, 'sessions', 'session');
INSERT INTO `django_content_type` VALUES (8, 'table_example_con', 'news');
COMMIT;

-- ----------------------------
-- Table structure for django_migrations
-- ----------------------------
DROP TABLE IF EXISTS `django_migrations`;
CREATE TABLE `django_migrations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `app` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `applied` datetime(6) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of django_migrations
-- ----------------------------
BEGIN;
INSERT INTO `django_migrations` VALUES (1, 'contenttypes', '0001_initial', '2020-05-20 08:22:30.343310');
INSERT INTO `django_migrations` VALUES (2, 'auth', '0001_initial', '2020-05-20 08:22:36.686042');
INSERT INTO `django_migrations` VALUES (3, 'admin', '0001_initial', '2020-05-20 08:22:38.200447');
INSERT INTO `django_migrations` VALUES (4, 'admin', '0002_logentry_remove_auto_add', '2020-05-20 08:22:38.263875');
INSERT INTO `django_migrations` VALUES (5, 'admin', '0003_logentry_add_action_flag_choices', '2020-05-20 08:22:38.319144');
INSERT INTO `django_migrations` VALUES (6, 'contenttypes', '0002_remove_content_type_name', '2020-05-20 08:22:39.304886');
INSERT INTO `django_migrations` VALUES (7, 'auth', '0002_alter_permission_name_max_length', '2020-05-20 08:22:39.390963');
INSERT INTO `django_migrations` VALUES (8, 'auth', '0003_alter_user_email_max_length', '2020-05-20 08:22:39.500366');
INSERT INTO `django_migrations` VALUES (9, 'auth', '0004_alter_user_username_opts', '2020-05-20 08:22:39.555123');
INSERT INTO `django_migrations` VALUES (10, 'auth', '0005_alter_user_last_login_null', '2020-05-20 08:22:39.968731');
INSERT INTO `django_migrations` VALUES (11, 'auth', '0006_require_contenttypes_0002', '2020-05-20 08:22:40.024269');
INSERT INTO `django_migrations` VALUES (12, 'auth', '0007_alter_validators_add_error_messages', '2020-05-20 08:22:40.056640');
INSERT INTO `django_migrations` VALUES (13, 'auth', '0008_alter_user_username_max_length', '2020-05-20 08:22:40.138795');
INSERT INTO `django_migrations` VALUES (14, 'auth', '0009_alter_user_last_name_max_length', '2020-05-20 08:22:40.241090');
INSERT INTO `django_migrations` VALUES (15, 'sessions', '0001_initial', '2020-05-20 08:22:40.667458');
INSERT INTO `django_migrations` VALUES (16, 'table_example_con', '0001_initial', '2020-05-20 09:41:40.228040');
INSERT INTO `django_migrations` VALUES (17, 'auth', '0010_alter_group_name_max_length', '2020-05-22 13:57:09.568385');
INSERT INTO `django_migrations` VALUES (18, 'auth', '0011_update_proxy_permissions', '2020-05-22 13:57:09.615713');
COMMIT;

-- ----------------------------
-- Table structure for django_session
-- ----------------------------
DROP TABLE IF EXISTS `django_session`;
CREATE TABLE `django_session` (
  `session_key` varchar(40) NOT NULL,
  `session_data` longtext NOT NULL,
  `expire_date` datetime(6) NOT NULL,
  PRIMARY KEY (`session_key`),
  KEY `django_session_expire_date_a5c62663` (`expire_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of django_session
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for medicine
-- ----------------------------
DROP TABLE IF EXISTS `medicine`;
CREATE TABLE `medicine` (
  `ID` char(10) NOT NULL,
  `medicine_name` varchar(30) NOT NULL,
  `scale` varchar(20) NOT NULL,
  `batch_num` varchar(20) DEFAULT NULL,
  `approval_num` varchar(20) NOT NULL,
  `effects` varchar(100) DEFAULT NULL,
  `bzq` varchar(10) DEFAULT NULL,
  `runit_price` float NOT NULL,
  `wunit_price` float NOT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of medicine
-- ----------------------------
BEGIN;
INSERT INTO `medicine` VALUES ('2018214782', '神奇R 气血康口服液', '10ml*12支', 'F7473247', '国药准字Z20003073', '健脾固本，滋阴润燥，生津止咳。用于神倦乏力，气短心悸，阴虚津少，口干舌燥。', '1年', 30.34, 22.755);
INSERT INTO `medicine` VALUES ('2018214783', '神奇R 抗病毒口服液', '10ml*15支', 'C120213', '国药准字Z32021255', '清热祛湿，凉血解毒。用于风热感冒，流感。', '1年', 34, 25.5);
INSERT INTO `medicine` VALUES ('2018214784', '消朦片', '30片*300盒', '201206A', '国药准字Z45021267', '明目退翳，镇静安神。用于角膜云翳、斑翳、白斑、白内障及神经衰弱。', '10个月', 40.2, 30.15);
INSERT INTO `medicine` VALUES ('2018214785', '银花芒果片', '9片*3板', 'CA12A', '国药准字Z20080642', '疏风清热，止咳化痰。用于外感风热所致的咽痛，喉痒，咳嗽，及上呼吸道感染见有上述症状者。', '1年', 10.78, 8.08);
INSERT INTO `medicine` VALUES ('2018214786', '四季感冒片', '0.35g*12片', '1B2346', '国药准字Z20093606', '清热解表。用于四季风寒感冒引起的发热头痛，鼻流清涕，咳嗽口干，咽喉疼痛，恶心厌食。', '2年', 50.78, 38.085);
INSERT INTO `medicine` VALUES ('2018214787', '风湿安泰片', '0.28g*12片', 'VW1753E', '国药准字Z14020316', '舒筋活血，祛风镇痛。用于筋骨麻木，手足拘挛，腰腿疼痛，风湿性关节炎。', '5个月', 17.54, 13.155);
INSERT INTO `medicine` VALUES ('2018214788', '骨刺平片', '铝塑 12片', '33672S', ' 国药准字Z14020590', '补精壮髓,壮筋健骨，通络止痛。用于骨质增生(包括肥大性腰椎炎，胸椎炎，颈椎综合症，四支骨节增生)。', '6个月', 40.77, 30.5775);
INSERT INTO `medicine` VALUES ('2018214789', '防芷鼻炎片', '0.3g×12片', '3469H', '国药准字Z14020254', '清热消炎，祛风通窍。用于治疗慢性鼻炎引起的喷嚏、鼻塞、头痛、过敏性鼻炎、慢性鼻窦炎。', '2年', 40.79, 30.5925);
INSERT INTO `medicine` VALUES ('2018214790', '清咽片', '0.25克×12片', 'H58345', '国药准字Z14020324', '清凉解热，生津止渴。用于咽喉肿痛，声嘶音哑，口干舌燥，咽下不利。', '1年', 57.87, 43.4025);
INSERT INTO `medicine` VALUES ('2018214791', '益肺胶囊', '0.3g*12粒', '34467G', '国药准字Z14020261', '补肾益肺，清热化痰，止咳平喘。用于久病咳喘，胸满多痰。', '5个月', 45.58, 34.185);
INSERT INTO `medicine` VALUES ('2018214792', '净石灵片', '0.3g*10片', 'FG4572', '国药准字Z20080439', '补肾、利尿、排石。用于治疗肾结石、输尿管结石、膀胱结石以及由结石引起的肾盂积水、尿路感染等。', '10个月', 24.57, 18.4275);
INSERT INTO `medicine` VALUES ('2018214793', '甲亢灵颗粒', '9g×18袋', 'FL58924', '国药准字Z20060400', '平肝潜阳，软坚散结。用于具有心悸、汗多、烦躁易怒、咽干、脉数等症状的甲状腺机能亢进症。', '1年', 35.35, 26.5125);
INSERT INTO `medicine` VALUES ('2018214794', '消渴降糖胶囊', '0.3g×12粒×3板', 'AH6532', '国药准字Z14021660', '生津止渴，甘平养胃，涩敛固阴。用于多饮，多尿，多食，消瘦, 体倦无力，尿糖及血糖升高之消渴症；轻度及中度成年型糖尿病。', '7个月', 72.7, 54.525);
INSERT INTO `medicine` VALUES ('2018214795', '化瘀祛斑胶囊', '0.32克×10粒×5板', 'LH5543', '国药准字Z14020939', '疏风清热，活血化瘀。用于黄褐斑、酒渣、粉刺。', '10个月', 87.1, 65.325);
INSERT INTO `medicine` VALUES ('2018214796', '六经头痛片', '0.25克×12片×3板', '2H7734', '国药准字Z14020322', '疏风活络，止痛利窍。用于全头痛、偏头痛及局部头痛。', '2年', 13.34, 10.005);
INSERT INTO `medicine` VALUES ('2018214797', '天蚕片', '0.3g*12片*2板', 'AJ569', '国药准字Z14020327', '核风定惊，化痰散结。用于惊风抽搐，咽喉肿痛，颌下淋巴结炎，面神经麻痹，皮肤瘙痒。', '3年', 47.41, 35.5575);
INSERT INTO `medicine` VALUES ('2018214798', '除脂生发片', '0.3g×12片×5板 200盒/件', 'KU9057', '国药准字Z14020253', '滋阴，养血，祛风，活络，止痒，除油脂。用于脂溢性脱发，头皮瘙痒，落屑，油脂分泌过多症。', '2年', 12.78, 9.585);
INSERT INTO `medicine` VALUES ('2018214799', '健肝灵胶囊', '0.5g×12粒×2板', 'H6894', '国药准字Z14020320', '益气健脾，活血化瘀；具有降低谷丙转氨酶的作用。用于急性、迁延性、慢性肝炎。', '3年', 43.71, 32.7825);
INSERT INTO `medicine` VALUES ('2018214800', '暖胃舒乐片', '0.25克×12片×3板', 'GF5345', '国药准字Z14020259', '温中补虚，调和肝脾，行气活血，止痛生肌。用于脾胃虚寒及肝脾不和型慢性胃炎症见脘腹疼痛，腹胀喜温，反酸嗳气。', '6个月', 12.78, 9.585);
INSERT INTO `medicine` VALUES ('2018214801', '救尔心胶囊', '0.45g×12粒×3板', 'KS459', '国药准字Z14020256', '活血通脉，化瘀生新。用于冠心病，心绞痛。', '4个月', 43.52, 32.64);
INSERT INTO `medicine` VALUES ('2018214802', '通脉胶囊', '0.5g×12粒×2板', 'LS086', '国药准字Z20060169', '活血通脉，用于缺血性心脑血管疾病，动脉硬化，脑血栓，脑缺血冠心病，心绞痛。', '1年', 28.72, 21.54);
INSERT INTO `medicine` VALUES ('2018214803', '石黄抗菌胶囊', '每粒装0.35g', 'DSY965', '国药准字Z20090132', '抗菌消炎。用于菌痢，上呼吸道感染，扁桃体炎，尿路感染。', '10个月', 40.2, 30.15);
INSERT INTO `medicine` VALUES ('2018214804', '逍遥丸(板裝 浓缩丸)', '48丸/板×6板', 'H87897', '国药准字Z34020541', '疏肝健脾，养血调经。用于肝气不舒所致月经不调，胸胁胀痛，头晕目眩，食欲减退。', '2年', 10.78, 8.085);
INSERT INTO `medicine` VALUES ('2018214805', '六味地黄丸', '48丸/板×6板', 'IU6994', '国药准字Z20055532', '滋阴补肾。用于头晕耳鸣，腰膝酸软，遗精盗汗。', '1年', 50.78, 38.085);
INSERT INTO `medicine` VALUES ('2018214806', '归脾丸', '48丸/板×6板', 'GH56779', '国药准字Z34020592', '益气健脾，养血安神。用于心脾两虚，气短心悸，失眠多梦，头昏头晕，肢倦乏力，食欲不振。', '6个月', 17.54, 13.155);
INSERT INTO `medicine` VALUES ('2018214807', '明目地黄丸', '48丸/板×6板', 'E5768', '国药准字Z34020536', '滋肾，养肝，明目。用于肝肾阴虚，目涩畏光，视物模糊，迎风流泪。', '4个月', 40.77, 30.5775);
INSERT INTO `medicine` VALUES ('2018214808', '杞菊地黄丸', '48丸/板×6板', '5439HG', '国药准字Z34020557', '滋肾养肝。用于肝肾阴亏，眩晕耳鸣，羞明畏光，迎风流泪，视物昏花。', '3个月', 40.79, 30.5925);
INSERT INTO `medicine` VALUES ('2018214809', '银杏露', '120ml*1瓶', '415354HJ', '国药准字Z34020428', '止咳，化痰，定喘。用于慢性支气管炎咳嗽，排痰不爽，久咳气喘。', '10个月', 57.87, 43.4025);
INSERT INTO `medicine` VALUES ('2018214810', '白云山R 葡萄糖酸锌口服溶液', '10ml:35mg', '453452F', '国药准字H20059936', '用于治疗缺锌引起的营养不良、厌食症、异食癖、口腔溃疡、痤疮、儿童生长发育迟缓等。', '2年', 45.58, 34.185);
INSERT INTO `medicine` VALUES ('2018214811', '夏桑菊颗粒', '3g×15袋', '23543GF', '国药准字Z44022217', '清肝明目，疏风散热，除湿痹，解疮毒。用于风热感冒，目赤头痛，头晕耳鸣，咽喉肿痛，疔疮肿毒等症，并可作清凉饮料。', '10个月', 24.57, 18.4275);
INSERT INTO `medicine` VALUES ('2018214812', '二丁颗粒（浓缩型）', '4g*10袋/盒', '9644GH', '国药准字Z19993153', '清热解毒。用于火热毒盛所致的热疖痈毒、咽喉肿痛、风热火眼。', '2年', 35.35, 26.5125);
INSERT INTO `medicine` VALUES ('2018214813', '科伦 奥美拉唑肠溶胶囊', '20mg*28粒', '8965G', '国药准字H20065335', '适用于胃溃疡、十二指肠溃疡、应激性溃疡、反流性食管炎和卓-艾氏综合征（胃泌素瘤）', '1年', 57.87, 43.4025);
INSERT INTO `medicine` VALUES ('2018214814', '小儿氨酚黄那敏片', '20片', '90874HG', '国药准字H23022614', '适用于缓解儿童普通感冒及流行性感冒引起的发热、头痛、四肢酸痛、打喷嚏、流鼻涕、鼻塞、咽痛等症状。', '5个月', 45.58, 34.185);
INSERT INTO `medicine` VALUES ('2018214815', '盐酸氟桂利嗪片', '40片', '254JH', '国药准字H10920064', '1）脑供血不足，椎动脉缺血，脑血栓形成后等。（2）耳鸣，脑晕。（3）偏头痛预防。（4）癫痫辅助治疗。', '2年', 24.57, 18.4275);
INSERT INTO `medicine` VALUES ('2018214816', '银翘解毒合剂', '10ml*6支', '967TY65', '国药准字Z11020130', '辛凉解表，清热解毒。用于风热感冒，发热头痛，咳嗽，口干，咽喉疼痛。', '4个月', 35.35, 6.5125);
INSERT INTO `medicine` VALUES ('2018214817', '板蓝根颗粒', '6g*6袋', '289TR6', '国药准字Z11020474', '清热解毒，凉血利咽。用于肺胃热盛所致的咽喉肿痛、口咽干燥；急性扁桃体炎见上述证候者。', '5个月', 72.7, 54.525);
INSERT INTO `medicine` VALUES ('2018214818', '小儿咳喘灵颗粒', '10g*10袋', '6589H2', '国药准字Z11020128', '宣肺、清热，止咳、祛痰。用于上呼吸道感染引起的咳嗽。', '7个月', 87.1, 65.325);
INSERT INTO `medicine` VALUES ('2018214819', '银翘解毒合剂', '每支装10ml', '89DF76', '国药准字Z11020130', '辛凉解表，清热解毒。用于风热感冒，发热头痛，咳嗽，口干，咽喉疼痛。', '3个月', 13.34, 10.005);
INSERT INTO `medicine` VALUES ('2018214820', '六味地黄口服液', '10ml*10支', '24HYJ89', '国药准字Z10910019', '滋阴补肾。用于头晕耳鸣，腰膝酸软，遗精盗汗。', '7个月', 47.41, 35.5575);
INSERT INTO `medicine` VALUES ('2018214821', '风湿安泰片', '0.28g*12片*2板', '254JKI8', '国药准字Z14020316', '舒筋活血，祛风镇痛。用于筋骨麻木，手足拘挛，腰腿疼痛，风湿性关节炎。', '1年', 12.78, 9.585);
COMMIT;

-- ----------------------------
-- Table structure for medicine_producer
-- ----------------------------
DROP TABLE IF EXISTS `medicine_producer`;
CREATE TABLE `medicine_producer` (
  `medicine_ID` char(10) NOT NULL,
  `producer_ID` char(10) NOT NULL,
  `unit_price` float NOT NULL,
  PRIMARY KEY (`medicine_ID`,`producer_ID`) USING BTREE,
  KEY `producer_ID` (`producer_ID`) USING BTREE,
  CONSTRAINT `medicine_producer_ibfk_1` FOREIGN KEY (`medicine_ID`) REFERENCES `medicine` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `medicine_producer_ibfk_2` FOREIGN KEY (`producer_ID`) REFERENCES `producer` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of medicine_producer
-- ----------------------------
BEGIN;
INSERT INTO `medicine_producer` VALUES ('2018214782', '1956148001', 18.204);
INSERT INTO `medicine_producer` VALUES ('2018214782', '1956148259', 15.753);
INSERT INTO `medicine_producer` VALUES ('2018214783', '1956148259', 20.4);
INSERT INTO `medicine_producer` VALUES ('2018214784', '2059856301', 24.12);
INSERT INTO `medicine_producer` VALUES ('2018214785', '2059856301', 6.468);
INSERT INTO `medicine_producer` VALUES ('2018214786', '1589410756', 30.468);
INSERT INTO `medicine_producer` VALUES ('2018214787', '1208963478', 10.524);
INSERT INTO `medicine_producer` VALUES ('2018214788', '1208963478', 24.462);
INSERT INTO `medicine_producer` VALUES ('2018214789', '1208963478', 24.474);
INSERT INTO `medicine_producer` VALUES ('2018214790', '1208963478', 34.722);
INSERT INTO `medicine_producer` VALUES ('2018214791', '1208963478', 27.348);
INSERT INTO `medicine_producer` VALUES ('2018214792', '1208963478', 14.742);
INSERT INTO `medicine_producer` VALUES ('2018214792', '1596302845', 18.364);
INSERT INTO `medicine_producer` VALUES ('2018214792', '4186012741', 15.984);
INSERT INTO `medicine_producer` VALUES ('2018214792', '5932478013', 16.842);
INSERT INTO `medicine_producer` VALUES ('2018214793', '1208963478', 21.21);
INSERT INTO `medicine_producer` VALUES ('2018214794', '1208963478', 43.62);
INSERT INTO `medicine_producer` VALUES ('2018214795', '1208963478', 52.26);
INSERT INTO `medicine_producer` VALUES ('2018214795', '1956148259', 50.572);
INSERT INTO `medicine_producer` VALUES ('2018214795', '2059856301', 48.378);
INSERT INTO `medicine_producer` VALUES ('2018214796', '1208963478', 8.004);
INSERT INTO `medicine_producer` VALUES ('2018214797', '1208963478', 28.446);
INSERT INTO `medicine_producer` VALUES ('2018214797', '4186012741', 25.181);
INSERT INTO `medicine_producer` VALUES ('2018214798', '1208963478', 7.668);
INSERT INTO `medicine_producer` VALUES ('2018214799', '1208963478', 26.226);
INSERT INTO `medicine_producer` VALUES ('2018214800', '1208963478', 7.668);
INSERT INTO `medicine_producer` VALUES ('2018214800', '4186012741', 6.867);
INSERT INTO `medicine_producer` VALUES ('2018214800', '9635410862', 7.522);
INSERT INTO `medicine_producer` VALUES ('2018214801', '1208963478', 26.112);
INSERT INTO `medicine_producer` VALUES ('2018214802', '1208963478', 17.232);
INSERT INTO `medicine_producer` VALUES ('2018214803', '1208963478', 24.12);
INSERT INTO `medicine_producer` VALUES ('2018214804', '1596302845', 6.468);
INSERT INTO `medicine_producer` VALUES ('2018214805', '1596302845', 30.468);
INSERT INTO `medicine_producer` VALUES ('2018214806', '1596302845', 10.524);
INSERT INTO `medicine_producer` VALUES ('2018214807', '1596302845', 24.462);
INSERT INTO `medicine_producer` VALUES ('2018214808', '1596302845', 24.474);
INSERT INTO `medicine_producer` VALUES ('2018214808', '4186012741', 22.281);
INSERT INTO `medicine_producer` VALUES ('2018214808', '9635401896', 25.882);
INSERT INTO `medicine_producer` VALUES ('2018214808', '9635410862', 29.812);
INSERT INTO `medicine_producer` VALUES ('2018214809', '1596302845', 34.722);
INSERT INTO `medicine_producer` VALUES ('2018214810', '9635410862', 27.348);
INSERT INTO `medicine_producer` VALUES ('2018214811', '9635410862', 14.742);
INSERT INTO `medicine_producer` VALUES ('2018214812', '9635410862', 21.21);
INSERT INTO `medicine_producer` VALUES ('2018214813', '9635410862', 34.722);
INSERT INTO `medicine_producer` VALUES ('2018214814', '5932478013', 27.348);
INSERT INTO `medicine_producer` VALUES ('2018214815', '1208963478', 18.848);
INSERT INTO `medicine_producer` VALUES ('2018214815', '1596302845', 15.981);
INSERT INTO `medicine_producer` VALUES ('2018214815', '4186012741', 17.584);
INSERT INTO `medicine_producer` VALUES ('2018214815', '5932478013', 14.742);
INSERT INTO `medicine_producer` VALUES ('2018214815', '9635401896', 13.515);
INSERT INTO `medicine_producer` VALUES ('2018214816', '9635401896', 21.21);
INSERT INTO `medicine_producer` VALUES ('2018214817', '9635401896', 43.62);
INSERT INTO `medicine_producer` VALUES ('2018214818', '9635401896', 52.26);
INSERT INTO `medicine_producer` VALUES ('2018214819', '9635401896', 8.004);
INSERT INTO `medicine_producer` VALUES ('2018214820', '4186012741', 28.446);
INSERT INTO `medicine_producer` VALUES ('2018214821', '4186012741', 7.668);
COMMIT;

-- ----------------------------
-- Table structure for producer
-- ----------------------------
DROP TABLE IF EXISTS `producer`;
CREATE TABLE `producer` (
  `ID` char(10) NOT NULL,
  `producer_name` varchar(20) NOT NULL,
  `address` varchar(50) NOT NULL,
  `tel` char(11) DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of producer
-- ----------------------------
BEGIN;
INSERT INTO `producer` VALUES ('1208963478', '山西仁源堂药业有限公司', '山西省太原市杏花岭区仁源路188号', '19875419252');
INSERT INTO `producer` VALUES ('1589410756', '天津和治药业有限公司', '天津市北辰区医药医疗器械工业园腾旺道22 号', '15496581021');
INSERT INTO `producer` VALUES ('1596302845', '上海宝龙安庆药业有限公司', '上海昆仑小区28号', '15841209647');
INSERT INTO `producer` VALUES ('1956148001', '宁波立华制药有限公司', '宁波经济技术开发区海滨城路15号', '18945210147');
INSERT INTO `producer` VALUES ('1956148259', '上海六合堂生物科技项城制药有限公司', '上海朝阳小区10号', '15985329654');
INSERT INTO `producer` VALUES ('2059856301', '广西南珠制药有限公司', '广西经济技术开发区武华大道8号', '14596323287');
INSERT INTO `producer` VALUES ('4186012741', '恒拓集团广西圣康制药有限公司', '广西东盟经济技术开发区武华大道158号', '13740896248');
INSERT INTO `producer` VALUES ('5932478013', '哈药集团制药六厂', '北京市北辰区59 号', '13854109874');
INSERT INTO `producer` VALUES ('9635401896', '北京长城制药有限公司', '北京市甘井子区营城子工业园区', '15987416654');
INSERT INTO `producer` VALUES ('9635410862', '广州白云山星群(药业)股份有限公司', '广州幸福路41号', '15741096347');
COMMIT;

-- ----------------------------
-- Table structure for purchasedetail
-- ----------------------------
DROP TABLE IF EXISTS `purchasedetail`;
CREATE TABLE `purchasedetail` (
  `ID` char(10) NOT NULL,
  `medicine_ID` char(10) NOT NULL,
  `producer_ID` char(10) NOT NULL,
  `purchase_quantity` int(11) NOT NULL,
  `total_price` float NOT NULL,
  PRIMARY KEY (`ID`,`medicine_ID`,`producer_ID`) USING BTREE,
  KEY `medicine_ID` (`medicine_ID`) USING BTREE,
  KEY `producer_ID` (`producer_ID`) USING BTREE,
  CONSTRAINT `purchasedetail_ibfk_1` FOREIGN KEY (`medicine_ID`) REFERENCES `medicine` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `purchasedetail_ibfk_2` FOREIGN KEY (`producer_ID`) REFERENCES `producer` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of purchasedetail
-- ----------------------------
BEGIN;
INSERT INTO `purchasedetail` VALUES ('1405709867', '2018214804', '1596302845', 400, 2587.2);
INSERT INTO `purchasedetail` VALUES ('1405709867', '2018214805', '1596302845', 350, 10663.8);
INSERT INTO `purchasedetail` VALUES ('1496164073', '2018214800', '1208963478', 400, 3067.2);
INSERT INTO `purchasedetail` VALUES ('1496164073', '2018214800', '9635410862', 20, 150.44);
INSERT INTO `purchasedetail` VALUES ('1496164073', '2018214801', '1208963478', 530, 13839.4);
INSERT INTO `purchasedetail` VALUES ('1496164073', '2018214802', '1208963478', 90, 1550.88);
INSERT INTO `purchasedetail` VALUES ('1634737576', '2018214803', '1208963478', 150, 3618);
INSERT INTO `purchasedetail` VALUES ('1693143789', '2018214792', '1596302845', 750, 13773);
INSERT INTO `purchasedetail` VALUES ('1693143789', '2018214792', '4186012741', 120, 1918.08);
INSERT INTO `purchasedetail` VALUES ('1693143789', '2018214792', '5932478013', 450, 7578.9);
INSERT INTO `purchasedetail` VALUES ('2349770292', '2018214807', '1596302845', 230, 5626.26);
INSERT INTO `purchasedetail` VALUES ('2349770292', '2018214808', '1596302845', 420, 10279.1);
INSERT INTO `purchasedetail` VALUES ('3070953880', '2018214820', '4186012741', 500, 14223);
INSERT INTO `purchasedetail` VALUES ('3070953880', '2018214821', '4186012741', 300, 2300.4);
INSERT INTO `purchasedetail` VALUES ('3228998237', '2018214817', '9635401896', 450, 19629);
INSERT INTO `purchasedetail` VALUES ('3228998237', '2018214818', '9635401896', 700, 36582);
INSERT INTO `purchasedetail` VALUES ('3228998237', '2018214819', '9635401896', 800, 6403.2);
INSERT INTO `purchasedetail` VALUES ('3907907802', '2018214795', '1956148259', 490, 24780.3);
INSERT INTO `purchasedetail` VALUES ('3907907802', '2018214795', '2059856301', 380, 18383.6);
INSERT INTO `purchasedetail` VALUES ('3907907802', '2018214808', '4186012741', 220, 4901.82);
INSERT INTO `purchasedetail` VALUES ('4012710816', '2018214796', '1208963478', 50, 400.2);
INSERT INTO `purchasedetail` VALUES ('4049904209', '2018214785', '2059856301', 200, 1293.6);
INSERT INTO `purchasedetail` VALUES ('4049904209', '2018214786', '1589410756', 1000, 30468);
INSERT INTO `purchasedetail` VALUES ('4049904209', '2018214787', '1208963478', 700, 7366.8);
INSERT INTO `purchasedetail` VALUES ('4084612837', '2018214783', '1956148001', 300, 6120);
INSERT INTO `purchasedetail` VALUES ('4084612837', '2018214784', '2059856301', 300, 7236);
INSERT INTO `purchasedetail` VALUES ('4586285903', '2018214800', '4186012741', 530, 3639.51);
INSERT INTO `purchasedetail` VALUES ('4683637257', '2018214809', '1596302845', 1000, 34722);
INSERT INTO `purchasedetail` VALUES ('4683637257', '2018214810', '9635410862', 350, 9571.8);
INSERT INTO `purchasedetail` VALUES ('5196140486', '2018214782', '1956148001', 100, 1820.4);
INSERT INTO `purchasedetail` VALUES ('5204765731', '2018214797', '4186012741', 650, 16367.7);
INSERT INTO `purchasedetail` VALUES ('5204765731', '2018214808', '9635401896', 760, 19670.3);
INSERT INTO `purchasedetail` VALUES ('5204765731', '2018214808', '9635410862', 860, 25638.3);
INSERT INTO `purchasedetail` VALUES ('5204765731', '2018214815', '4186012741', 120, 2110.08);
INSERT INTO `purchasedetail` VALUES ('5204765731', '2018214815', '9635401896', 950, 12839.2);
INSERT INTO `purchasedetail` VALUES ('5228890137', '2018214815', '1208963478', 620, 11685.8);
INSERT INTO `purchasedetail` VALUES ('5228890137', '2018214815', '1596302845', 730, 11666.1);
INSERT INTO `purchasedetail` VALUES ('5356898929', '2018214802', '1208963478', 550, 9477.6);
INSERT INTO `purchasedetail` VALUES ('5356898929', '2018214803', '1208963478', 750, 18090);
INSERT INTO `purchasedetail` VALUES ('6361281344', '2018214797', '1208963478', 900, 25601.4);
INSERT INTO `purchasedetail` VALUES ('6361281344', '2018214798', '1208963478', 100, 766.8);
INSERT INTO `purchasedetail` VALUES ('6361281344', '2018214799', '1208963478', 900, 23603.4);
INSERT INTO `purchasedetail` VALUES ('6361281344', '2018214800', '1208963478', 800, 6134.4);
INSERT INTO `purchasedetail` VALUES ('6361281344', '2018214801', '1208963478', 700, 18278.4);
INSERT INTO `purchasedetail` VALUES ('8041871009', '2018214806', '1596302845', 450, 4735.8);
INSERT INTO `purchasedetail` VALUES ('8041871009', '2018214807', '1596302845', 350, 8561.7);
INSERT INTO `purchasedetail` VALUES ('8041871009', '2018214808', '1596302845', 100, 2447.4);
INSERT INTO `purchasedetail` VALUES ('8041871009', '2018214809', '1596302845', 100, 3472.2);
INSERT INTO `purchasedetail` VALUES ('8041871009', '2018214810', '9635410862', 1000, 27348);
INSERT INTO `purchasedetail` VALUES ('8041871009', '2018214811', '9635410862', 700, 10319.4);
INSERT INTO `purchasedetail` VALUES ('8041871009', '2018214812', '9635410862', 650, 13786.5);
INSERT INTO `purchasedetail` VALUES ('8041871009', '2018214813', '9635410862', 300, 10416.6);
INSERT INTO `purchasedetail` VALUES ('8041871009', '2018214814', '5932478013', 750, 20511);
INSERT INTO `purchasedetail` VALUES ('8041871009', '2018214815', '5932478013', 150, 2211.3);
INSERT INTO `purchasedetail` VALUES ('8041871009', '2018214816', '9635401896', 600, 12726);
INSERT INTO `purchasedetail` VALUES ('8402749677', '2018214788', '1208963478', 550, 13454.1);
INSERT INTO `purchasedetail` VALUES ('8402749677', '2018214789', '1208963478', 250, 6118.5);
INSERT INTO `purchasedetail` VALUES ('8402749677', '2018214790', '1208963478', 200, 6944.4);
INSERT INTO `purchasedetail` VALUES ('8402749677', '2018214791', '1208963478', 400, 10939.2);
INSERT INTO `purchasedetail` VALUES ('8402749677', '2018214792', '1208963478', 950, 14004.9);
INSERT INTO `purchasedetail` VALUES ('8402749677', '2018214793', '1208963478', 900, 19089);
INSERT INTO `purchasedetail` VALUES ('8402749677', '2018214794', '1208963478', 50, 2181);
INSERT INTO `purchasedetail` VALUES ('8402749677', '2018214795', '1208963478', 1000, 52260);
INSERT INTO `purchasedetail` VALUES ('8946388763', '2018214782', '1956148259', 650, 10239.5);
COMMIT;

-- ----------------------------
-- Table structure for purchaseinfo
-- ----------------------------
DROP TABLE IF EXISTS `purchaseinfo`;
CREATE TABLE `purchaseinfo` (
  `ID` char(10) NOT NULL,
  `purchase_date` date NOT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of purchaseinfo
-- ----------------------------
BEGIN;
INSERT INTO `purchaseinfo` VALUES ('1405709867', '2019-02-02');
INSERT INTO `purchaseinfo` VALUES ('1496164073', '2015-12-01');
INSERT INTO `purchaseinfo` VALUES ('1634737576', '2015-11-13');
INSERT INTO `purchaseinfo` VALUES ('1693143789', '2016-12-17');
INSERT INTO `purchaseinfo` VALUES ('2349770292', '2015-02-16');
INSERT INTO `purchaseinfo` VALUES ('3070953880', '2017-03-26');
INSERT INTO `purchaseinfo` VALUES ('3228998237', '2018-07-15');
INSERT INTO `purchaseinfo` VALUES ('3907907802', '2016-02-02');
INSERT INTO `purchaseinfo` VALUES ('4012710816', '2019-09-03');
INSERT INTO `purchaseinfo` VALUES ('4049904209', '2020-03-05');
INSERT INTO `purchaseinfo` VALUES ('4586285903', '2015-12-02');
INSERT INTO `purchaseinfo` VALUES ('4683637257', '2015-01-29');
INSERT INTO `purchaseinfo` VALUES ('5196140486', '2018-07-30');
INSERT INTO `purchaseinfo` VALUES ('5204765731', '2016-02-06');
INSERT INTO `purchaseinfo` VALUES ('5228890137', '2016-05-03');
INSERT INTO `purchaseinfo` VALUES ('5356898929', '2019-04-22');
INSERT INTO `purchaseinfo` VALUES ('6361281344', '2019-07-26');
INSERT INTO `purchaseinfo` VALUES ('8041871009', '2018-11-03');
INSERT INTO `purchaseinfo` VALUES ('8402749677', '2020-02-28');
INSERT INTO `purchaseinfo` VALUES ('8946388763', '2017-01-07');
COMMIT;

-- ----------------------------
-- Table structure for selldetail
-- ----------------------------
DROP TABLE IF EXISTS `selldetail`;
CREATE TABLE `selldetail` (
  `ID` char(10) NOT NULL,
  `medicine_ID` char(10) NOT NULL,
  `sell_quantity` int(11) NOT NULL,
  `total_price` float NOT NULL,
  PRIMARY KEY (`ID`,`medicine_ID`) USING BTREE,
  KEY `medicine_ID` (`medicine_ID`) USING BTREE,
  CONSTRAINT `selldetail_ibfk_1` FOREIGN KEY (`medicine_ID`) REFERENCES `medicine` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of selldetail
-- ----------------------------
BEGIN;
INSERT INTO `selldetail` VALUES ('1086273953', '2018214808', 50, 1529.62);
INSERT INTO `selldetail` VALUES ('1086273953', '2018214809', 450, 19531.1);
INSERT INTO `selldetail` VALUES ('1086273953', '2018214810', 55, 1880.18);
INSERT INTO `selldetail` VALUES ('1153650874', '2018214813', 35, 1519.09);
INSERT INTO `selldetail` VALUES ('1153650874', '2018214814', 345, 11793.8);
INSERT INTO `selldetail` VALUES ('1153650874', '2018214815', 20, 368.55);
INSERT INTO `selldetail` VALUES ('1227836729', '2018214785', 70, 565.95);
INSERT INTO `selldetail` VALUES ('1227836729', '2018214786', 675, 25707.4);
INSERT INTO `selldetail` VALUES ('1227836729', '2018214787', 200, 2631);
INSERT INTO `selldetail` VALUES ('1227836729', '2018214788', 100, 3057.75);
INSERT INTO `selldetail` VALUES ('1227836729', '2018214789', 140, 4282.95);
INSERT INTO `selldetail` VALUES ('1430240756', '2018214792', 760, 14004.9);
INSERT INTO `selldetail` VALUES ('1430240756', '2018214793', 160, 4242);
INSERT INTO `selldetail` VALUES ('1430240756', '2018214794', 25, 1363.12);
INSERT INTO `selldetail` VALUES ('1430240756', '2018214795', 60, 3919.5);
INSERT INTO `selldetail` VALUES ('1430240756', '2018214796', 45, 450.225);
INSERT INTO `selldetail` VALUES ('1449933342', '2018214795', 130, 8492.25);
INSERT INTO `selldetail` VALUES ('1449933342', '2018214808', 360, 11013.3);
INSERT INTO `selldetail` VALUES ('1475199100', '2018214790', 1, 57.87);
INSERT INTO `selldetail` VALUES ('1475199100', '2018214791', 2, 91.16);
INSERT INTO `selldetail` VALUES ('1825640587', '2018214800', 1, 12.78);
INSERT INTO `selldetail` VALUES ('2138344690', '2018214797', 150, 5333.62);
INSERT INTO `selldetail` VALUES ('2138344690', '2018214798', 50, 479.25);
INSERT INTO `selldetail` VALUES ('3043498794', '2018214810', 1, 45.58);
INSERT INTO `selldetail` VALUES ('3072190891', '2018214811', 210, 3869.77);
INSERT INTO `selldetail` VALUES ('3072190891', '2018214812', 420, 11135.2);
INSERT INTO `selldetail` VALUES ('3693619620', '2018214808', 1, 40.79);
INSERT INTO `selldetail` VALUES ('3938016640', '2018214782', 350, 7964.25);
INSERT INTO `selldetail` VALUES ('3938016640', '2018214819', 420, 4202.1);
INSERT INTO `selldetail` VALUES ('3938016640', '2018214820', 140, 4978.05);
INSERT INTO `selldetail` VALUES ('3938016640', '2018214821', 15, 143.775);
INSERT INTO `selldetail` VALUES ('3971049538', '2018214804', 3, 32.34);
INSERT INTO `selldetail` VALUES ('3971049538', '2018214805', 1, 50.78);
INSERT INTO `selldetail` VALUES ('3971049538', '2018214806', 1, 17.54);
INSERT INTO `selldetail` VALUES ('3971049538', '2018214807', 2, 81.54);
INSERT INTO `selldetail` VALUES ('3977892419', '2018214801', 390, 12729.6);
INSERT INTO `selldetail` VALUES ('3977892419', '2018214802', 20, 430.8);
INSERT INTO `selldetail` VALUES ('3977892419', '2018214803', 50, 1507.5);
INSERT INTO `selldetail` VALUES ('3977892419', '2018214807', 20, 611.55);
INSERT INTO `selldetail` VALUES ('4970000424', '2018214808', 1, 40.79);
INSERT INTO `selldetail` VALUES ('4970000424', '2018214809', 2, 115.74);
INSERT INTO `selldetail` VALUES ('5501410722', '2018214802', 455, 9800.7);
INSERT INTO `selldetail` VALUES ('5501410722', '2018214803', 490, 14773.5);
INSERT INTO `selldetail` VALUES ('5606300495', '2018214792', 500, 9213.75);
INSERT INTO `selldetail` VALUES ('5969151828', '2018214800', 150, 1437.75);
INSERT INTO `selldetail` VALUES ('7057282892', '2018214795', 340, 22210.5);
INSERT INTO `selldetail` VALUES ('7461507333', '2018214816', 35, 927.938);
INSERT INTO `selldetail` VALUES ('8155301092', '2018214817', 1, 72.7);
INSERT INTO `selldetail` VALUES ('8155301092', '2018214818', 1, 87.1);
INSERT INTO `selldetail` VALUES ('8368115973', '2018214815', 345, 6357.49);
INSERT INTO `selldetail` VALUES ('8469229227', '2018214797', 1, 40.79);
INSERT INTO `selldetail` VALUES ('9193411696', '2018214782', 50, 1137.75);
INSERT INTO `selldetail` VALUES ('9193411696', '2018214783', 160, 4080);
INSERT INTO `selldetail` VALUES ('9193411696', '2018214784', 150, 4522.5);
INSERT INTO `selldetail` VALUES ('9825350709', '2018214799', 545, 17866.5);
INSERT INTO `selldetail` VALUES ('9825350709', '2018214800', 150, 1437.75);
INSERT INTO `selldetail` VALUES ('9825350709', '2018214801', 155, 5059.2);
INSERT INTO `selldetail` VALUES ('9910477571', '2018214792', 1, 24.57);
INSERT INTO `selldetail` VALUES ('9910477571', '2018214815', 1, 24.57);
COMMIT;

-- ----------------------------
-- Table structure for sellinfo
-- ----------------------------
DROP TABLE IF EXISTS `sellinfo`;
CREATE TABLE `sellinfo` (
  `ID` char(10) NOT NULL,
  `sell_date` date NOT NULL,
  `way` char(2) NOT NULL,
  `customer` char(10) DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  KEY `customer` (`customer`) USING BTREE,
  CONSTRAINT `sellinfo_ibfk_1` FOREIGN KEY (`customer`) REFERENCES `customer` (`ID`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of sellinfo
-- ----------------------------
BEGIN;
INSERT INTO `sellinfo` VALUES ('1086273953', '2020-05-30', '批发', '8888936870');
INSERT INTO `sellinfo` VALUES ('1153650874', '2020-05-20', '批发', '8889790834');
INSERT INTO `sellinfo` VALUES ('1227836729', '2020-05-04', '批发', '8886065749');
INSERT INTO `sellinfo` VALUES ('1430240756', '2020-03-03', '批发', '8882385442');
INSERT INTO `sellinfo` VALUES ('1449933342', '2020-02-16', '批发', '8883341047');
INSERT INTO `sellinfo` VALUES ('1475199100', '2020-02-06', '零售', '8889909378');
INSERT INTO `sellinfo` VALUES ('1825640587', '2020-05-13', '零售', '8889790834');
INSERT INTO `sellinfo` VALUES ('2138344690', '2020-04-12', '批发', '8880517210');
INSERT INTO `sellinfo` VALUES ('3043498794', '2020-04-09', '零售', '8880806559');
INSERT INTO `sellinfo` VALUES ('3072190891', '2020-03-27', '批发', '8889713233');
INSERT INTO `sellinfo` VALUES ('3693619620', '2019-03-12', '零售', '8885338876');
INSERT INTO `sellinfo` VALUES ('3938016640', '2019-02-20', '批发', '8882744886');
INSERT INTO `sellinfo` VALUES ('3971049538', '2019-01-26', '零售', '8886319155');
INSERT INTO `sellinfo` VALUES ('3977892419', '2019-12-14', '批发', '8888936870');
INSERT INTO `sellinfo` VALUES ('4970000424', '2019-09-19', '零售', '8883341047');
INSERT INTO `sellinfo` VALUES ('5501410722', '2019-02-16', '批发', '8883949946');
INSERT INTO `sellinfo` VALUES ('5606300495', '2019-02-11', '批发', '8887511216');
INSERT INTO `sellinfo` VALUES ('5969151828', '2020-01-30', '批发', '8889713233');
INSERT INTO `sellinfo` VALUES ('7057282892', '2020-01-18', '批发', '8880806559');
INSERT INTO `sellinfo` VALUES ('7461507333', '2020-01-08', '批发', '8888936870');
INSERT INTO `sellinfo` VALUES ('8155301092', '2019-10-22', '零售', '8887542604');
INSERT INTO `sellinfo` VALUES ('8368115973', '2019-09-14', '批发', '8881577453');
INSERT INTO `sellinfo` VALUES ('8469229227', '2020-01-24', '零售', '8885882751');
INSERT INTO `sellinfo` VALUES ('9825350709', '2019-10-30', '批发', '8880916120');
INSERT INTO `sellinfo` VALUES ('9910477571', '2019-10-26', '零售', '8885107482');
COMMIT;

-- ----------------------------
-- Table structure for store
-- ----------------------------
DROP TABLE IF EXISTS `store`;
CREATE TABLE `store` (
  `medicine_ID` char(10) NOT NULL,
  `quantity` int(11) NOT NULL,
  `pos` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`medicine_ID`) USING BTREE,
  CONSTRAINT `store_ibfk_1` FOREIGN KEY (`medicine_ID`) REFERENCES `medicine` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of store
-- ----------------------------
BEGIN;
INSERT INTO `store` VALUES ('2018214782', 600, '1区441');
INSERT INTO `store` VALUES ('2018214783', 150, '4区4258');
INSERT INTO `store` VALUES ('2018214784', 150, '1区441');
INSERT INTO `store` VALUES ('2018214785', 130, '7区4');
INSERT INTO `store` VALUES ('2018214786', 325, '7区83');
INSERT INTO `store` VALUES ('2018214787', 500, '1区441');
INSERT INTO `store` VALUES ('2018214788', 450, '1区441');
INSERT INTO `store` VALUES ('2018214789', 110, '20区98');
INSERT INTO `store` VALUES ('2018214790', 199, NULL);
INSERT INTO `store` VALUES ('2018214791', 398, NULL);
INSERT INTO `store` VALUES ('2018214792', 1009, NULL);
INSERT INTO `store` VALUES ('2018214793', 740, '5区7');
INSERT INTO `store` VALUES ('2018214794', 25, '5区7');
INSERT INTO `store` VALUES ('2018214795', 1340, '15区17');
INSERT INTO `store` VALUES ('2018214796', 5, '8区48');
INSERT INTO `store` VALUES ('2018214797', 1499, '4区985');
INSERT INTO `store` VALUES ('2018214798', 50, '7区83');
INSERT INTO `store` VALUES ('2018214799', 355, '1区441');
INSERT INTO `store` VALUES ('2018214800', 1449, '5区7');
INSERT INTO `store` VALUES ('2018214801', 685, '8区6');
INSERT INTO `store` VALUES ('2018214802', 165, '4区4258');
INSERT INTO `store` VALUES ('2018214803', 360, '8区963');
INSERT INTO `store` VALUES ('2018214804', 397, '4区4258');
INSERT INTO `store` VALUES ('2018214805', 349, '15区17');
INSERT INTO `store` VALUES ('2018214806', 449, '8区48');
INSERT INTO `store` VALUES ('2018214807', 328, '75区88');
INSERT INTO `store` VALUES ('2018214808', 1708, NULL);
INSERT INTO `store` VALUES ('2018214809', 648, NULL);
INSERT INTO `store` VALUES ('2018214810', 1294, '36区84');
INSERT INTO `store` VALUES ('2018214811', 490, NULL);
INSERT INTO `store` VALUES ('2018214812', 230, NULL);
INSERT INTO `store` VALUES ('2018214813', 265, '4区985');
INSERT INTO `store` VALUES ('2018214814', 405, NULL);
INSERT INTO `store` VALUES ('2018214815', 884, NULL);
INSERT INTO `store` VALUES ('2018214816', 565, '8区6');
INSERT INTO `store` VALUES ('2018214817', 449, NULL);
INSERT INTO `store` VALUES ('2018214818', 699, NULL);
INSERT INTO `store` VALUES ('2018214819', 380, '12区749');
INSERT INTO `store` VALUES ('2018214820', 360, NULL);
INSERT INTO `store` VALUES ('2018214821', 285, '65区43');
COMMIT;

-- ----------------------------
-- Table structure for workerinfo
-- ----------------------------
DROP TABLE IF EXISTS `workerinfo`;
CREATE TABLE `workerinfo` (
  `ID` char(10) NOT NULL,
  `worker_name` varchar(20) NOT NULL,
  `gender` char(1) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `tel` char(11) DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of workerinfo
-- ----------------------------
BEGIN;
INSERT INTO `workerinfo` VALUES ('1000346572', '韩义翼', '女', '1988-03-07', '19058191752');
INSERT INTO `workerinfo` VALUES ('1001132173', '孙新故', '女', '1983-08-18', '17203958565');
INSERT INTO `workerinfo` VALUES ('1001242010', '陈府襟', '女', '1980-01-10', '12758723446');
INSERT INTO `workerinfo` VALUES ('1003119204', '陈带星', '男', '1982-11-16', '11064392568');
INSERT INTO `workerinfo` VALUES ('1003203704', '秦襟府', '男', '1982-07-21', '18037891694');
INSERT INTO `workerinfo` VALUES ('1003358961', '郑豫业', '男', '1993-08-16', '16006398741');
INSERT INTO `workerinfo` VALUES ('1003435894', '王五故', '女', '1990-08-10', '15413119367');
INSERT INTO `workerinfo` VALUES ('1003791180', '许江立', '女', '1985-08-25', '15856502491');
INSERT INTO `workerinfo` VALUES ('1003881527', '郑衡国', '男', '1992-12-23', '17317451238');
INSERT INTO `workerinfo` VALUES ('1004474156', '何分湖', '女', '2000-07-24', '11318875053');
INSERT INTO `workerinfo` VALUES ('1004628226', '王星五', '女', '1996-11-30', '12652663834');
INSERT INTO `workerinfo` VALUES ('1005814986', '孙章庐', '男', '1982-05-09', '18570265734');
INSERT INTO `workerinfo` VALUES ('1005953883', '王接地', '男', '1988-03-15', '12074900894');
INSERT INTO `workerinfo` VALUES ('1006140789', '赵庐义', '女', '1999-07-29', '12184784199');
INSERT INTO `workerinfo` VALUES ('1006164778', '陶都章', '女', '1990-07-27', '17672343634');
INSERT INTO `workerinfo` VALUES ('1006724680', '吕立江', '男', '1988-12-29', '19677296434');
INSERT INTO `workerinfo` VALUES ('1007440449', '陈业豫', '男', '1993-04-28', '15511892485');
INSERT INTO `workerinfo` VALUES ('1009039604', '朱衡新', '女', '1998-08-18', '15501399889');
INSERT INTO `workerinfo` VALUES ('1009113493', '蒋轸庐', '男', '1986-02-23', '14570172087');
COMMIT;

-- ----------------------------
-- View structure for medicine_producerInfoView
-- ----------------------------
DROP VIEW IF EXISTS `medicine_producerInfoView`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `medicine_producerInfoView` AS select `medicine`.`ID` AS `medicine_id`,`medicine`.`medicine_name` AS `medicine_name`,`producer`.`ID` AS `producer_id`,`producer`.`producer_name` AS `producer_name`,`medicine_producer`.`unit_price` AS `unit_price` from ((`medicine` join `producer`) join `medicine_producer`) where ((`medicine`.`ID` = `medicine_producer`.`medicine_ID`) and (`producer`.`ID` = `medicine_producer`.`producer_ID`));

-- ----------------------------
-- View structure for PurchaseInfoView
-- ----------------------------
DROP VIEW IF EXISTS `PurchaseInfoView`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `PurchaseInfoView` AS select `purchasedetail`.`ID` AS `id`,`purchasedetail`.`medicine_ID` AS `medicine_id`,`medicine`.`medicine_name` AS `medicine_name`,`purchasedetail`.`producer_ID` AS `producer_id`,`producer`.`producer_name` AS `producer_name`,`purchaseinfo`.`purchase_date` AS `purchase_date`,`medicine_producer`.`unit_price` AS `unit_price`,`purchasedetail`.`purchase_quantity` AS `purchase_quantity`,`purchasedetail`.`total_price` AS `total_price` from ((((`purchaseinfo` join `purchasedetail`) join `medicine`) join `producer`) join `medicine_producer`) where ((`purchaseinfo`.`ID` = `purchasedetail`.`ID`) and (`purchasedetail`.`medicine_ID` = `medicine_producer`.`medicine_ID`) and (`purchasedetail`.`producer_ID` = `medicine_producer`.`producer_ID`) and (`purchasedetail`.`medicine_ID` = `medicine`.`ID`) and (`purchasedetail`.`producer_ID` = `producer`.`ID`));

-- ----------------------------
-- View structure for SellInfoView
-- ----------------------------
DROP VIEW IF EXISTS `SellInfoView`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `SellInfoView` AS select `selldetail`.`ID` AS `id`,`selldetail`.`medicine_ID` AS `medicine_id`,`medicine`.`medicine_name` AS `medicine_name`,`sellinfo`.`sell_date` AS `sell_date`,`selldetail`.`sell_quantity` AS `sell_quantity`,`selldetail`.`total_price` AS `total_price`,`sellinfo`.`customer` AS `customer_id`,`customer`.`customer_name` AS `customer_name`,`sellinfo`.`way` AS `way`,(case `sellinfo`.`way` when '批发' then `medicine`.`wunit_price` when '零售' then `medicine`.`runit_price` end) AS `unit_price` from (((`sellinfo` join `selldetail`) join `medicine`) join `customer`) where ((`sellinfo`.`ID` = `selldetail`.`ID`) and (`selldetail`.`medicine_ID` = `medicine`.`ID`) and (`sellinfo`.`customer` = `customer`.`ID`));

-- ----------------------------
-- Triggers structure for table purchasedetail
-- ----------------------------
DROP TRIGGER IF EXISTS `purchaseInsertTrigger`;
delimiter ;;
CREATE TRIGGER `purchaseInsertTrigger` AFTER INSERT ON `purchasedetail` FOR EACH ROW begin
	update store set quantity=quantity+NEW.purchase_quantity 
	 where store.medicine_id = new.medicine_id; 
end
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table selldetail
-- ----------------------------
DROP TRIGGER IF EXISTS `sellInsertTrigger`;
delimiter ;;
CREATE TRIGGER `sellInsertTrigger` AFTER INSERT ON `selldetail` FOR EACH ROW begin
	update store set quantity=quantity-NEW.sell_quantity 
	 where store.medicine_id = new.medicine_id; 
end
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;
